Brief Full Advanced Search Search Tips
|  |  | To access the contents, click the chapter and section titles. Visual Basic 6 Programming Blue Book: The Most Complete, Hands-On Resource for Writing Programs with Microsoft Visual Basic 6! (Publisher: The Coriolis Group) Author(s): Peter G. Aitken ISBN: 1576102815 Publication Date: 08/01/98 Search this book:
Previous | Table of Contents | Next
#### A Front-End Demonstration We now have the tools we need to create a Visual Basic front end. We know how to start another Windows program and send it commands and data. The demonstration program developed here is fundamental, but you’ll be surprised at how easily you can create even a full-featured front end. This program is designed for use with Excel, but the same techniques will apply to other applications as well. FRONTEND’s data-entry form is shown in Figure 9.3. Figure 9.3 A Visual Basic program for data entry into an Excel spreadsheet. Here are the tasks we want it to perform: • Start Excel. • Display three Text Box controls for data entry. • Poke data in the three Text Boxes to columns A, B, and C in the Excel spreadsheet, starting in the first row. • Poke subsequent entries into the same columns in row 2, row 3, and so on. • Display an updating label on the form that informs the user of the current record number. • On exit, prompt for a file-name entry and send commands to Excel to save the spreadsheet under that name; then terminate both itself and Excel. The program’s single form contains three Text Boxes, four Labels, and two Command Buttons. The Text Boxes and Command Buttons are control arrays. Starting with this project, I will be using a new method to describe a form’s properties and controls. Rather than describe the steps to you, I will present a list of forms, controls, and properties in the form shown in Listing 9.3. This is, in fact, the same format that Visual Basic uses in a FRM file; you can use WordPad or any other text editor to open a FRM file to see what I mean. The format used is hierarchical, as follows:
    Begin VB_Form FormName
        [form properties here in alphabetical order]
        Begin VB_CommandButton CommandButtonName
            [CommandButton properties here in alphabetical order]
        End
        Begin VB_TextBox TextBoxName
            [TextBox properties here in alphabetical order]
        End
    End
An actual FRM file lists all of the properties, but I will include only those you need to explicitly change. For the FRONTEND demonstration program’s form, the objects and properties are shown in Listing 9.4. For convenient data entry, ensure that the tab order has the three Text Box controls in the 1, 2, and 3 positions, and the Next button in the 4 position. Listing 9.4 Objects and properties in FRONTEND.FRM.
    Begin VB.Form Form1
       Caption         =   “Data entry form”
       LinkTopic       =   “Form1”
       Begin VB.CommandButton Command1
          Caption          =   “Done”
          Index            =   1
          TabIndex         =   8
       End
       Begin VB.CommandButton Command1
          Caption          =   “Next”
          Index            =   0
          TabIndex         =   7
       End
       Begin VB.TextBox Text1
          Index            =   2
          TabIndex         =   6
       End
       Begin VB.TextBox Text1
          Index            =   1
          TabIndex         =   5
       End
       Begin VB.TextBox Text1
          Index            =   0
          TabIndex         =   4
       End
       Begin VB.Label Label4
          Alignment        =   1  ‘Right Justify
          Caption          =   “Cost each:”
       End
       Begin VB.Label Label3
          Alignment        =   1  ‘Right Justify
          Caption          =   “Quantity:”
       End
       Begin VB.Label Label2
          Alignment        =   1  ‘Right Justify
          Caption          =   “Item:”
       End
       Begin VB.Label Label1
       End
    End
The program’s code is presented in Listing 9.5, and you can see that it is rather short. A flag named ExcelRunning is maintained: True if Excel has been started, and False otherwise. This flag is used to prevent the program from trying to send commands to Excel if a DDE link has not been established (which occurs when the user “saves” the first record). This would cause an error. The remainder of the code is straightforward and should be clearly understandable from the comments. Listing 9.5 Code in FRONTEND.FRM.
    Option Explicit

    Const NONE = 0, MANUAL = 2
    Dim ExcelRunning As Boolean

    Private Sub Command1_Click(Index As Integer)
    Dim Cmd As String
    Dim Filename As String
    Dim Prompt As String
    Dim X As Long
    Dim Item As String
    Static Row As Integer

    On Local Error GoTo Errorhandler

    Select Case Index
        Case 0      ‘ Next
            If Not ExcelRunning Then
                ‘ Start Excel minimized without focus. Be sure to put
                ‘ the path to your copy of Excel here.
                X = Shell(“D:\MSOFFICE97\OFFICE\EXCEL.EXE”, vbMinimizedNoFocus)
                ExcelRunning = True
            End If

            ‘ Poke the data to the Excel spreadsheet. Loop once
            ‘ for each Text Box in the control array. Excel always
            ‘ starts with the default spreadsheet name SHEET1.
            Row = Row + 1

            For X = 0 To 2
                Text1(X).LinkMode = NONE
                Text1(X).LinkTopic = “EXCEL|SHEET1”
                Item = “R” & Right$(Str$(Row), Len(Str$(Row)) - 1)
                Item = Item & “C” & Right$(Str$(X + 1), Len(Str$(X + 1)) - 1)
                Text1(X).LinkItem = Item
                Text1(X).LinkMode = MANUAL
                Text1(X).LinkPoke
            Next X

            ‘ Clear the Text Boxes.
            For X = 0 To 2
                Text1(X).TEXT = “”
            Next X

            ‘ Set focus to the first Text Box.
            Text1(0).SetFocus

            ‘ Update the counter label.
            Label1.Caption = “Entering record” & Str$(Row + 1)

        Case 1      ‘ Done

            ‘ If Excel is running, save workbook and close.
            If ExcelRunning Then
                ‘ Get the name for the Excel file.
                Prompt = “Name for Excel file (no extension)”
                Filename = InputBox(Prompt, “File name”, “”)
                Filename = Filename & “.XLS”

                ‘ Save the spreadsheet. CHR$(34) is the double quote character.
                Cmd = “[SAVE.AS(” & Chr$(34) & Filename & Chr$(34) & “)]”
                Text1(2).LinkExecute Cmd

                ‘ Close Excel.
                Text1(2).LinkExecute “[Quit()]”
            End If
            End
    End Select

    Exit Sub

    Errorhandler:
        Resume Next

    End Sub

    Private Sub Form_Load()

    ‘ Display record number.
    Label1.Caption = “Entering record 1”

    ExcelRunning = False

    End Sub
Note that I have used the On Local Error statement: When an error is detected, an error-handling routine is executed that contains simply a Resume Next statement. This tells Visual Basic to ignore errors without reporting them (you will learn details of Visual Basic error handling in Chapter 25). This step was necessary because the program would sometimes report a “DDE time out” error, meaning that Excel had not responded to the DDE request within the allotted time limit. In fact, Excel was responding and everything was working fine—just a bit slowly. You can prevent this type of error by increasing the destination control’s LinkTimeOut property. This demonstration program omits most of the error handling and other “idiot proofing” code that a program designed for distribution should include. Even so, it’s a good illustration of how Visual Basic and DDE can be teamed up with commercial applications to provide the best solution to a customer’s needs.
Previous | Table of Contents | Next

| Products | Contact Us | About Us | Privacy | Ad Info | Home Use of this site is subject to certain Terms & Conditions, Copyright © 1996-2000 EarthWeb Inc. All rights reserved. Reproduction whole or in part in any form or medium without express written permission of EarthWeb is prohibited. Read EarthWeb's privacy statement.